﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace PMSSys
{
    public class DBconnentor
    {
        private MySqlConnection MariaConnector;


        public bool OpenConnection(String database, String host, String username, String password, String port)
        {
            MariaConnector = new MySqlConnection("Database=" + database + "; Server=" + host + "; Port=" + port + ";" +
                "Username=" + username + "; Password=" + password);

            try
            {
                MariaConnector.Open();
                return true;
            }
            catch (MySqlException el)
            {
                MessageBox.Show(el.Message.ToString(), "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
        }

        public bool closeConnection()
        {
            try
            {
                MariaConnector.Close();
                return true;
            }
            catch (MySqlException e)
            {
                MessageBox.Show(e.Message.ToString(), "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
        }

        public MySqlConnection getConnection()
        {
            return MariaConnector;
        }
        public string lastID()
        {
            //string strSQLSelect = "SELECT @@IDENTITY AS 'LastID'"; may error e
            //string strSQLSelect = "SELECT MAX(p_id) AS 'LastID' FROM PATIENT_PROFILE";
            //return (String)scalarView(strSQLSelect); me mali rin to amp
            string strSQLSelect = "SELECT LAST_INSERTED_ID()";
            return scalarView(strSQLSelect).ToString();


        }
        public DataTable view(String selectQueryString)
        {
            MySqlCommand selectQuery = new MySqlCommand(selectQueryString, MariaConnector);
            MySqlDataAdapter query = new MySqlDataAdapter(selectQuery);

            DataSet set = new DataSet();
            try
            {
                query.Fill(set);
                return set.Tables[0];
            }
            catch (MySqlException e)
            {
                MessageBox.Show(e.Message.ToString(), "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }

        }
        public Object scalarView(String selectQueryString)
        {
            MySqlCommand selectQuery = new MySqlCommand(selectQueryString, MariaConnector);
            try
            {
                object tmp = selectQuery.ExecuteScalar();
                return tmp;
            }
            catch (MySqlException e)
            {
                MessageBox.Show(e.Message.ToString(), "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }

        }
        public bool nonQueryExecute(MySqlCommand query) {
            try
            {
                query.ExecuteNonQuery();
                return true;
            }
            catch (MySqlException e)
            {
                MessageBox.Show(e.Message.ToString(), "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
        }
    }
}
